import xlrd, xlwt
from common.big_screen_interface import BigScreenInterface
import json
import time

class WriteExcel():
    def __init__(self,sheet_name):
        # 创建一个workbook 设置编码  和worksheet
        self.wb = xlwt.Workbook(encoding='utf-8')
        self.ws = self.wb.add_sheet(sheet_name)

    def write_excel_head(self,head_col0,head_col1,head_col2,head_col3,head_col4,head_col5,head_col6,):
        self.ws.write(0, 0,head_col0)
        self.ws.write(0, 1,head_col1)
        self.ws.write(0, 2,head_col2)
        self.ws.write(0, 3, head_col3)
        self.ws.write(0, 4, head_col4)
        self.ws.write(0, 5, head_col5)
        self.ws.write(0, 6, head_col6)

    def write_excel_body(self, row, col, value):
        # 数据写入excel,参数对应 行, 列, 值
        self.ws.write(row, col, value)  # 不带样式的写入

    def save_excel(self,excel_name):
        current_time = time.strftime("%Y-%m-%d_%H_%M_%S", time.localtime(time.time()))
        self.wb.save('/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/data_{}_{}.xls'.format(excel_name,current_time))



class ReadExcel(object):
    def __init__(self, data_path, sheetname, wenxin_step1_url,wenxin_step1_static):
        #定义一个属性接收文件路径
        self.data_path = data_path
        # 定义一个属性接收工作表名称
        self.sheetname = sheetname
        # 使用xlrd模块打开excel表读取数据
        self.data = xlrd.open_workbook(self.data_path)
        # 根据工作表的名称获取工作表中的内容
        self.table = self.data.sheet_by_name(self.sheetname)
        # 获取工作表的有效行数
        self.rowNum = self.table.nrows
        self.wenxin_step1_url = wenxin_step1_url
        self.wenxin_step1_static = wenxin_step1_static

    def readExcel(self):
        data = []
        for i in range(1, self.rowNum):
            # 获取单元格数据
            except_type = self.table.cell_value(i, 1)
            except_method = self.table.cell_value(i, 2)

            content = self.table.cell_value(i, 3)
            contents = content.split('\n')
            # print(contents)
            for content in contents:
                # 调用文心接口,拿到返回的type和对应的method
                # obj = BigScreenInterface(self.wenxin_step1_url)
                # res, r_elapsed = obj.wenxin_step1(content)
                # res_dic = json.loads(res)
                # 将content，返回type写入excel
                if self.sheetname != "数据分析类指令":
                    data.append({"content":content, "res":"res_dic","r_elapsed": "r_elapsed","except_type":except_type,"except_method":except_method})
                else:
                    # 数据分析指令，需要调用另一个接口
                    obj_statistic = BigScreenInterface(self.wenxin_step1_static)
                    text = content.split('查查')[1]
                    res_statistic, r_elapsed_statistics = obj_statistic.wenxin_step1(text)
                    res_statistic_dic = json.loads(res_statistic)
                    data.append({"content": text, "res": "res_dic", "res_statistic_dic": res_statistic_dic, "except_type": except_type,
                                 "except_method": except_method, "r_elapsed_statistics": r_elapsed_statistics,"r_elapsed": "r_elapsed"})
        return data

def generate_excel_body(data, obj,):
    # data值 [{"content":"放大地图"，"res":{……},"except_type":except_type,"except_method":except_method}, {},{}]
    for i in range(len(data)):
        # 1：第一列
        col_0 = data[i]["content"]
        # 2：第二列
        try:
            col_1 = data[i]["res"]["data"]["type"]
        except Exception as e:
            col_1 = "无data.type字段"
        # 3:第三列
        if sheetname == "控屏类指令":
            try:
                col_2 = data[i]["res"]["data"]["screenControl"]["method"]
            except Exception as e:
                col_2 = '返回值中无screenControl.method'
        elif sheetname == "闲聊类指令":
            try:
                col_2 = data[i]["res"]["data"]["chat"]["type"]
            except Exception as e:
                col_2 = '返回值中无chat.type'
        else:
            # 数据分析
            try:
                col_2 = data[i]["res_statistic_dic"]["data"]["type"]
            except Exception as e:
                col_2 = '第二个接口返回值中无data.type'
        # 4：第四列
        col_3 = data[i]["except_type"]

        # 5：第五列
        if sheetname != "数据分析类指令":
            col_4 = data[i]["except_method"]
        else:
            col_4 = col_3
        # 6：第七列
        if sheetname != "数据分析类指令":
            try:
                col_6 = str(data[i]["res"]["data"])
            except Exception as e:
                col_6 = '接口中返回值中无data字段'+ '\n' + str(data[i]["res"])
        else:
            try:
                col_6 = "耗时{}s".format(str(data[i]["r_elapsed_statistics"]))+str(data[i]["res_statistic_dic"]["data"])
            except Exception as e:
                col_6 = '第二个接口返回值中无data字段' + '\n' + str(data[i]["res_statistic_dic"])

        obj.write_excel_body(i+1,0,col_0)                        # 第i+1行，第1列
        obj.write_excel_body(i+1,1,col_1)                        # 第i+1行，第2列
        obj.write_excel_body(i+1,2,col_2)                        # 第i+1行，第3列
        obj.write_excel_body(i + 1, 3, col_3)                    # 第i+1行，第4列
        obj.write_excel_body(i + 1, 4, col_4)                    # 第i+1行，第5列
        obj.write_excel_body(i + 1, 6, col_6)                    # 第i+1行，第7列

        # 7、第六列：判断，给出结论
        if sheetname != "数据分析类指令":
            if col_1 == col_3 and col_2 == col_4:
                obj.write_excel_body(i + 1, 5, "PASS")
            else:
                obj.write_excel_body(i + 1, 5, "FAIL")
        else:
            if col_1 == col_2 == col_3:
                obj.write_excel_body(i + 1, 5, "PASS")
            else:
                obj.write_excel_body(i + 1, 5, "FAIL")
    obj.save_excel(sheetname)


def main(data_path,sheetname,wenxin_step1_url,wenxin_step1_static):
    read_obj = ReadExcel(data_path,sheetname,wenxin_step1_url,wenxin_step1_static)
    data = read_obj.readExcel()

    write_obj = WriteExcel(sheetname)
    if sheetname == "控屏类指令":
        write_obj.write_excel_head("指令类型","返回的type类型","返回的screenControl.method值","预期的type类型","预期的screenControl.method值","结论","备注")
    elif sheetname == "闲聊类指令":
        write_obj.write_excel_head("指令类型", "返回的type类型", "返回的chat.type值", "预期的type类型","预期的chat.type值", "结论","备注")
    else:
        write_obj.write_excel_head("指令类型", "返回的type类型", "返回数据分析接口type值", "预期的type类型", "预期数据分析接口type值", "结论", "备注")    # 数据分析类指令
    generate_excel_body(data, write_obj)


if __name__ == "__main__":

    data_path = "/bigScreenDigitalPeople/resource/大屏数字人指令集-test.xlsx"
    # sheetname_list = ["控屏类指令","闲聊类指令","数据分析类指令"]
    # sheetname_list = ["控屏类指令"]
    # sheetname_list = ["闲聊类指令"]
    sheetname_list = ["数据分析类指令"]
    wenxin_step1_url = "https://219.135.214.196:9001/v2x/platform/wenxin/chat"
    wenxin_step1_static = "https://219.135.214.196:9001/v2x/platform/wenxin/chat/statistic"

    for sheetname in sheetname_list:

        main(data_path,sheetname,wenxin_step1_url,wenxin_step1_static)








